﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BM.DBMapping;
using System.Data.OracleClient;

namespace Cinser.Common.Database
{
    public class OracleDAL
    {

        /// <summary>
        /// pMinIndex
        /// </summary>
        /// <param name="pDBSession"></param>
        /// <param name="pTableName"></param>
        /// <param name="pSqlWhere"></param>
        /// <param name="pMinIndex">pMinIndex == -1则表示取全部</param>
        /// <param name="pItemCount"></param>
        /// <returns></returns>
        public DataTable GetDataTable(IDBSession pDBSession, string pTableName, string pSqlWhere, int pageIndex, int onePageItemCount)
        {
            pDBSession.Open();
            string mySql = "select * from " + pTableName;
            if (pSqlWhere.Length > 0)
            {
                mySql += " where " + pSqlWhere;
            }

            System.Data.IDbCommand myDBCommand = pDBSession.DbConnection.CreateCommand();
            if (pageIndex == -1)
            {
                myDBCommand.CommandText = mySql;
            }
            else
            {
                myDBCommand.CommandText = this.GetPageOracleSqlString(mySql, pageIndex, onePageItemCount);
            }
            OracleDataAdapter myOracleDataAdapter = new OracleDataAdapter(myDBCommand as OracleCommand);
            DataSet myDataSet = new DataSet();
            myOracleDataAdapter.Fill(myDataSet);
            pDBSession.Close();
            if (myDataSet.Tables.Count == 0)
            {
                return null;
            }
            else
            {
                return myDataSet.Tables[0];
            }
        }


        /// <summary>
        /// 查询记录个数
        /// </summary>
        /// <param name="pDBSession"></param>
        /// <param name="pTabelName"></param>
        /// <param name="pSqlWhere"></param>
        /// <returns></returns>
        public int GetCount(IDBSession pDBSession, string pTabelName, string pSqlWhere)
        {
            pDBSession.Open();
            string mySql = "select Count(*) from " + pTabelName;
            if (pSqlWhere.Length > 0)
            {
                mySql += " where " + pSqlWhere;
            }
            System.Data.IDbCommand myDBCommand = pDBSession.DbConnection.CreateCommand();
            myDBCommand.CommandText = mySql;
            int myCount = Convert.ToInt32(myDBCommand.ExecuteScalar());
            pDBSession.Close();
            return myCount;
        }




        private string GetPageOracleSqlString(string pBaseSqlString, int pageIndex, int onePageItemCount)
        {
            System.Text.StringBuilder mySqlStrings = new StringBuilder();
            mySqlStrings.Append("select * from (select BM.*,ROWNUM as BMRowNum from (");
            mySqlStrings.Append(pBaseSqlString);
            mySqlStrings.Append(") BM");
            //ROWNUM<=10*2) where BMRowNum >=10*(2-1)
            mySqlStrings.Append(" where ROWNUM<=" + pageIndex + "*" + onePageItemCount + ")");
            mySqlStrings.Append(" where BMRowNum >" + (pageIndex - 1) + "*" + onePageItemCount);
            return mySqlStrings.ToString();
        }


    }
}
